package com.nus.database;

import com.nus.beans.Leave;
import com.nus.beans.LeaveStatus;
import com.nus.services.ConversionService;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by IntelliJ IDEA.
 * User: dell
 * Date: 9/22/12
 * Time: 10:48 PM
 * To change this template use File | Settings | File Templates.
 */
public class LeavesDBManager extends DBManager {

    public LeavesDBManager(Connection connection) {
        super(connection);
    }

    public List<Leave> getLeavesHistory(int userId) {
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Leave> leaves = new ArrayList<Leave>();
        Leave leave;
        int leaveStatusDB;
        ConversionService conversionService = new ConversionService();
        LeaveStatus leaveStatus;

        try {
            statement = connection.prepareStatement(SQLStatements.VIEW_LEAVE_HISTORY);
            statement.setInt(1, userId);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                leave = new Leave();
                leave.setDate(resultSet.getDate("LEAVEDATE"));
                leave.setAppliedDate(resultSet.getDate("APPLIED_DATE"));
                leave.setVerifiedDate(resultSet.getDate("VERIFIED_DATE"));
                leave.setVerifiedBy(resultSet.getInt("VERIFIED_BY"));
                leave.setApprovedDate(resultSet.getDate("APPROVED_DATE"));
                leave.setApprovedBy(resultSet.getInt("APPROVED_BY"));
                leaveStatusDB = resultSet.getInt("STATUS");
                leave.setRejectedDate(resultSet.getDate("REJECTED_DATE"));
                leave.setVerifiedByName(resultSet.getString("VERIFIED_NAME"));
                leave.setApprovedByName(resultSet.getString("APPROVED_NAME"));
                leave.setRejectedByName(resultSet.getString("REJECTED_NAME"));
                leaveStatus = conversionService.getLeaveStatus(leaveStatusDB);
                leave.setStatus(leaveStatus);
                leaves.add(leave);
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement, resultSet);
        }
        return leaves;
    }

    public void applyLeave(Leave leave) {
        PreparedStatement statement = null;
        setAutoCommit(false);
        try {
            addLeaveRequest(leave);
            decrementLeave(leave.getAppliedBy());
            commit();
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
            rollback();
        } finally {
            close(statement);
            setAutoCommit(true);
        }
    }

    public boolean isLeaveApplicable(Leave leave) {
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        boolean isLeaveApplicable = true;
        try {
            statement = connection.prepareStatement(SQLStatements.CHECK_LEAVE_APPLICABLE_SQL);
            statement.setInt(1, leave.getAppliedBy());
            statement.setDate(2, new java.sql.Date(leave.getDate().getTime()));
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                isLeaveApplicable = false;
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement, resultSet);
        }
        return isLeaveApplicable;
    }

    private void addLeaveRequest(Leave leave) throws SQLException {
        ConversionService conversionService = new ConversionService();
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(SQLStatements.ADD_LEAVE_REQUEST);
            statement.setInt(1, leave.getAppliedBy());
            statement.setDate(2, new java.sql.Date(leave.getDate().getTime()));

            int leaveStatus = conversionService.getLeaveStatus(leave.getStatus());
            statement.setInt(3, leaveStatus);
            statement.executeUpdate();
        } finally {
            close(statement);
        }
    }

    private void decrementLeave(int userId) throws SQLException {
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(SQLStatements.DECREASE_LEAVES);
            statement.setInt(1, userId);
            statement.executeUpdate();
        } finally {
           close(statement);
        }
    }

    public boolean isLeaveCanceleble(Leave leave) {
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        boolean isLeaveApplicable = true;
        try {
            statement = connection.prepareStatement(SQLStatements.CHECK_LEAVE_CANCELABLE_SQL);
            statement.setInt(1, leave.getAppliedBy());
            statement.setDate(2, new java.sql.Date(leave.getDate().getTime()));
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                isLeaveApplicable = false;
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement, resultSet);
        }
        return isLeaveApplicable;
    }

    public boolean isLeaveReapplycable(Leave leave) {
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        boolean isLeaveReApplicable = true;
        try {
            statement = connection.prepareStatement(SQLStatements.CHECK_LEAVE_REAPPLYALE_SQL);
            statement.setInt(1, leave.getAppliedBy());
            statement.setDate(2, new java.sql.Date(leave.getDate().getTime()));
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                isLeaveReApplicable = false;
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement, resultSet);
        }
        return isLeaveReApplicable;
    }

    public boolean isLeaveRejectable(Leave leave) {
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        boolean isLeaveApplicable = true;
        try {
            statement = connection.prepareStatement(SQLStatements.CHECK_LEAVE_REJECTABLE_SQL);
            statement.setInt(1, leave.getAppliedBy());
            statement.setDate(2, new java.sql.Date(leave.getDate().getTime()));
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                isLeaveApplicable = false;
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement, resultSet);
        }
        return isLeaveApplicable;
    }

    public void cancelLeave(Leave leave) {
        PreparedStatement statement = null;
        setAutoCommit(false);
        try {
            cancelLeaveRequest(leave);
            incrementLeave(leave.getAppliedBy());
            commit();
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
            rollback();
        } finally {
            close(statement);
            setAutoCommit(true);
        }
    }

    public void reapplyLeave(Leave leave) {

        try {
            setAutoCommit(false);
            reapplyLeaveRequest(leave);
            decrementLeave(leave.getAppliedBy());
            commit();
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
            rollback();
        } finally {
            setAutoCommit(true);
        }
    }

    public void reapplyLeaveRequest(Leave leave) throws SQLException {
        ConversionService conversionService = new ConversionService();
        PreparedStatement statement = null;
        try{
        statement = connection.prepareStatement(SQLStatements.REAPPLY_LEAVE_REQUEST);
        int leaveStatusDB = conversionService.getLeaveStatus(leave.getStatus());
        statement.setInt(1, leaveStatusDB);
        statement.setInt(2, leave.getAppliedBy());
        statement.setDate(3, new java.sql.Date(leave.getDate().getTime()));
        statement.executeUpdate();
        } finally {
            close(statement);
        }
    }


    private void cancelLeaveRequest(Leave leave) throws SQLException {
        ConversionService conversionService = new ConversionService();
        PreparedStatement statement = null;
        int cancelStatus = conversionService.getLeaveStatus(LeaveStatus.CANCELED);
        try {
            statement = connection.prepareStatement(SQLStatements.UPDATE_LEAVE_REQUEST);
            statement.setInt(1, cancelStatus);
            statement.setInt(2, leave.getAppliedBy());
            statement.setDate(3, new java.sql.Date(leave.getDate().getTime()));
            statement.executeUpdate();
            connection.commit();
        } finally {
            close(statement);
        }
    }

    private void incrementLeave(int userId) throws SQLException {
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(SQLStatements.INCREASE_LEAVES);
            statement.setInt(1, userId);
            statement.executeUpdate();
        } finally {
            close(statement);
        }
    }

    public List<Leave> getPendingLeaves(LeaveStatus leaveStatus) {
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Leave> leaves = new ArrayList<Leave>();
        Leave leave;
        ConversionService conversionService = new ConversionService();
        int leaveStatusDB = conversionService.getLeaveStatus(leaveStatus);
        try {
            statement = connection.prepareStatement(SQLStatements.GET_PENDING_LEAVES_SQL);
            statement.setInt(1, leaveStatusDB);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                leave = new Leave();
                leave.setAppliedBy(resultSet.getInt("USERID"));
                leave.setDate(resultSet.getDate("LEAVEDATE"));
                leave.setAppliedDate(resultSet.getDate("APPLIED_DATE"));
                leave.setVerifiedDate(resultSet.getDate("VERIFIED_DATE"));
                leave.setVerifiedBy(resultSet.getInt("VERIFIED_BY"));
                leave.setApprovedDate(resultSet.getDate("APPROVED_DATE"));
                leave.setApprovedBy(resultSet.getInt("APPROVED_BY"));
                leave.setAppliedByName(resultSet.getString("appliedname"));
                leave.setVerifiedByName(resultSet.getString("verifiedname"));
                leaveStatusDB = resultSet.getInt("STATUS");
                leaveStatus = conversionService.getLeaveStatus(leaveStatusDB);
                leave.setStatus(leaveStatus);
                leaves.add(leave);
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement, resultSet);
        }
        return leaves;
    }

    public boolean isLeaveVerifiable(Leave leave) {
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        boolean isLeaveApprovable = true;
        try {
            statement = connection.prepareStatement(SQLStatements.CHECK_LEAVE_VERIFIABLE_SQL);
            statement.setInt(1, leave.getAppliedBy());
            statement.setDate(2, new java.sql.Date(leave.getDate().getTime()));
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                isLeaveApprovable = false;
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement, resultSet);
        }
        return isLeaveApprovable;
    }

    public void verifyLeave(Leave leave) {
        PreparedStatement statement = null;
        try {
            ConversionService conversionService = new ConversionService();
            int verifyStatus = conversionService.getLeaveStatus(LeaveStatus.VERIFIED);
            statement = connection.prepareStatement(SQLStatements.VERIFY_LEAVE_REQUEST);
            statement.setInt(1, verifyStatus);
            statement.setInt(2, leave.getVerifiedBy());
            statement.setInt(3, leave.getAppliedBy());
            statement.setDate(4, new java.sql.Date(leave.getDate().getTime()));
            statement.executeUpdate();
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
            e.printStackTrace();
        } finally {
            close(statement);
        }
    }

    public boolean isLeaveApprovable(Leave leave) {
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        boolean isLeaveApprovable = true;
        try {
            statement = connection.prepareStatement(SQLStatements.CHECK_LEAVE_VERIFIABLE_SQL);
            statement.setInt(1, leave.getAppliedBy());
            statement.setDate(2, new java.sql.Date(leave.getDate().getTime()));
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                isLeaveApprovable = false;
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement, resultSet);
        }
        return isLeaveApprovable;
    }

    public void approveLeave(Leave leave) {
        PreparedStatement statement = null;
        try {
            ConversionService conversionService = new ConversionService();
            int verifyStatus = conversionService.getLeaveStatus(LeaveStatus.APPROVED);
            statement = connection.prepareStatement(SQLStatements.APPROVE_LEAVE_REQUEST);
            statement.setInt(1, verifyStatus);
            statement.setInt(2, leave.getApprovedBy());
            statement.setInt(3, leave.getAppliedBy());
            statement.setDate(4, new java.sql.Date(leave.getDate().getTime()));
            statement.executeUpdate();
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement);
        }
    }

    public void rejectLeave(Leave leave) {
        PreparedStatement statement = null;
        setAutoCommit(false);
        try {
            rejectLeaveRequest(leave);
            incrementLeave(leave.getAppliedBy());
            commit();
        } catch (SQLException e) {

            logger.error(e.getMessage(), e);
            rollback();
        } finally {
            close(statement);
            setAutoCommit(true);
        }
    }

    private void rejectLeaveRequest(Leave leave) throws SQLException {
        ConversionService conversionService = new ConversionService();
        PreparedStatement statement = null;
        int rejectStatus = conversionService.getLeaveStatus(LeaveStatus.REJECTED);
        try {
            statement = connection.prepareStatement(SQLStatements.REJECT_LEAVE_REQUEST);
            statement.setInt(1, rejectStatus);
            statement.setInt(2, leave.getRejectedBy());
            statement.setInt(3, leave.getAppliedBy());
            statement.setDate(4, new java.sql.Date(leave.getDate().getTime()));
            statement.executeUpdate();
        } finally {
            close(statement);
        }
    }

}
